﻿using System;
using System.Diagnostics.Contracts;
using Epi.Data;

namespace CDC.SampleShell.Infrastructure
{
    public class SqlDatabaseUpgrader : DatabaseUpgraderBase
    {
        public SqlDatabaseUpgrader(IDbDriver database)
            : base(database)
        {
            Contract.Requires(database != null);
        }

        public override int GetDatabaseVersion()
        {
            if (_version != -1) return _version; // return cached version number if we've already determined the version previously

            if (_database.TableExists("UpdateLog") || _database.TableExists("Changesets"))
            {
                _version = 0;
            }
            else
            {
                _version = 1;
            }

            return _version;
        }

        public override bool UpgradeDatabase()
        {
            _version = GetDatabaseVersion();

            if (_version <= 0)
            {
                // #1 - create the UpdateLog table (required for DB logging in MSSQL server environments)
                Query createUpdateLogTableQuery = _database.CreateQuery("CREATE TABLE dbo.UpdateLog (" +
                            "ID int not null identity(1,1), " +
                            "UDate datetime2 not null, " +
                            "Version nvarchar(32), " +
                            "UserName nvarchar(32), " +
                            "MACADDR nvarchar(128), " +
                            "Description nvarchar(512) " +
                                ");");

                _database.ExecuteNonQuery(createUpdateLogTableQuery);

                // #2 - create the Changesets table (required for multi-user support in MSSQL server environments)
                Query createChangesetsTableQuery = _database.CreateQuery("CREATE TABLE dbo.Changesets (" +
                                "ChangesetID nvarchar(64), " +
                                "UpdateType int not null, " +
                                "UserName nvarchar(32), " +
                                "MACADDR nvarchar(128), " +
                                "Description nvarchar(256), " +
                                "DestinationRecordID nvarchar(64), " +
                                "DestinationFormID int, " +
                                "CheckinDate datetime2 not null, " +
                                "Changeset int not null identity(1,1), " +
                                "Version nvarchar(32) " +
                                ");");

                _database.ExecuteNonQuery(createChangesetsTableQuery);

                _version = 1;
            }

            return true;
        }
    }
}
